package by.grsu.crs.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import by.grsu.crs.dao.StatusDao;
import by.grsu.crs.model.Status;

public class StatusDaoImpl implements StatusDao {
	private static final String INSERT_NEW_STATUS
		= "INSERT INTO statuses(type, text, notice) VALUES (?, ?, ?)";

	private static final String SELECT_ALL_STATUSES
		= "SELECT id, type, text, notice FROM statuses";
	
	private static final String SELECT_STATUS_BY_ID
		= "SELECT id, type, text, notice FROM statuses WHERE id = ?";
	
	private static final String SELECT_STATUSES_BY_TYPE
		= "SELECT id, type, text, notice FROM statuses WHERE type = ?";
	
	private static final String DELETE_STATUS_BY_ID
		= "DELETE FROM statuses WHERE id = ?;";
	
	private static final String UPDATE_STATUS_BY_ID
		= "UPDATE statuses SET type = ?, text = ?, notice = ? WHERE id = ?";
	
	private Connection dbConnection;
	private PreparedStatement preparedStatement = null;
	private ResultSet resultSet = null;
	
	public StatusDaoImpl(Connection dbConnection) {
		this.dbConnection = dbConnection;
	}
	
	public boolean addStatus(Status status) throws SQLException {
		try {
			preparedStatement = dbConnection.prepareStatement(INSERT_NEW_STATUS);
			preparedStatement.setString(1, status.getType().toString());
			preparedStatement.setString(2, status.getText());
			preparedStatement.setString(3, status.getNotice());
			if (preparedStatement.executeUpdate() == 1) {
				return true;
			}
		} catch(Exception e) {
			//TODO: log this.
			e.printStackTrace();
		} finally {
			preparedStatement.close();
		}
		
		return false;
	}
	
	public List<Status> getAllStatuses() throws SQLException {
		List<Status> statuses = new ArrayList<Status>();
		
		try {		
			preparedStatement = dbConnection.prepareStatement(SELECT_ALL_STATUSES);
			resultSet = preparedStatement.executeQuery();
			
			while (resultSet.next()) {
				statuses.add(new Status(Status.Type.valueOf(resultSet.getString("type")),
						resultSet.getString("text"),
						resultSet.getString("notice")));
			}
		} catch(Exception e) {
			//TODO: log this.
			e.printStackTrace();
		} finally {
			resultSet.close();
			preparedStatement.close();
		}
		
		return statuses;
	}
	
	public Status getStatusById(int id) throws SQLException {
		Status status = null;
		
		try {		
			preparedStatement = dbConnection.prepareStatement(SELECT_STATUS_BY_ID);
			preparedStatement.setInt(1, id);
			resultSet = preparedStatement.executeQuery();
			
			resultSet.last();			
			if(resultSet.getRow() != 0) {
				resultSet.first();
				
				Status.Type type = Status.Type.valueOf(resultSet.getString("type"));
				String text = resultSet.getString("text");
				String notice = resultSet.getString("notice");
				
				status = new Status(type, text, notice);
			}		
		} catch(Exception e) {
			//TODO: log this.
			e.printStackTrace();
		} finally {
			resultSet.close();
			preparedStatement.close();
		}
		
		return status;
	}
	
	public List<Status> getStatusesByType(Status.Type type) throws SQLException {
		List<Status> statuses = new ArrayList<Status>();
		
		try {		
			preparedStatement = dbConnection.prepareStatement(SELECT_STATUSES_BY_TYPE);
			preparedStatement.setString(1, type.toString());
			resultSet = preparedStatement.executeQuery();
			
			while (resultSet.next()) {
				statuses.add(new Status(Status.Type.valueOf(resultSet.getString("type")),
						resultSet.getString("text"),
						resultSet.getString("notice")));
			}		
		} catch(Exception e) {
			//TODO: log this.
			e.printStackTrace();
		} finally {
			resultSet.close();
			preparedStatement.close();
		}
		
		return statuses;
	}
	
	public boolean deleteStatusByID(int id) throws SQLException {		
		try {
			preparedStatement = dbConnection.prepareStatement(DELETE_STATUS_BY_ID);
			preparedStatement.setInt(1, id);
			if (preparedStatement.executeUpdate() == 1) {
				return true;
			}
		} catch(Exception e) {
			//TODO: log this.
			e.printStackTrace();
		} finally {
			preparedStatement.close();
		}
		
		return false;
	}
	
	public boolean updateStatusById(int id, Status status) throws SQLException {
		try {
			preparedStatement = dbConnection.prepareStatement(UPDATE_STATUS_BY_ID);
			preparedStatement.setString(1, status.getType().toString());
			preparedStatement.setString(2, status.getText());
			preparedStatement.setString(3, status.getNotice());
			if (preparedStatement.executeUpdate() == 1) {
				return true;
			}
		} catch(Exception e) {
			//TODO: log this.
			e.printStackTrace();
		} finally {
			preparedStatement.close();
		}
		
		return false;
	}
}
